package LuceneIndexCreate;

import LuceneIndexCreate.model.CatData;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by User on 2017/12/6.
 */
public class DB {

    public static void main(String[] args) {
        Long[] cids = new Long[] {40L,99L,50011665L,50004958L,50007218L,50008907L,50023575L,50018252L,50018264L,50019379L,50024186L,50023878L,50023724L,50023804L,50024449L,50024451L,50024612L,50025111L,50025618L,50025707L,50025968L,50026523L,50454031L,50230002L,50734010L,50488001L,50016891L,50017652L, 50014442L,50014811L, 50158001L};


        List<CatData> result = new ArrayList();
        for (Long cid : cids) {
            List<CatData> allChild = getAllChild(cid);
            result.addAll(allChild);
        }

        for (CatData catData : result) {
            System.out.print(catData.getCid()+",");
        }

    }

    public static List<CatData> getAllChild(Long cid) {
        List<CatData> list = new ArrayList<>();
        List<CatData> child = getChild(cid);
        for (CatData catData : child) {
            Boolean isParent = catData.getParent();
            if (isParent) {
                list.addAll(getAllChild(Long.valueOf(catData.getCid())));
            } else {
                list.add(catData);
            }
        }

        return list;
    }

    public static String getRealKeyword(String cid) {

        // 驱动程序名
        String driver = "com.mysql.jdbc.Driver";

        // URL指向要访问的数据库名scutcs
        String url = "jdbc:mysql://127.0.0.1:3306/tm_master";

        // MySQL配置时的用户名
        String user = "root";

        // MySQL配置时的密码
        String password = "082040";

        try {
            // 加载驱动程序
            Class.forName(driver);

            // 连续数据库
            Connection conn = DriverManager.getConnection(url, user, password);

            // statement用来执行SQL语句
            Statement statement = conn.createStatement();

            // 要执行的SQL语句
            String sql = "select * from item_cat_lucene2 where cid = " + cid;

            // 结果集
            ResultSet rs = statement.executeQuery(sql);


            if(rs.next()) {
                String name = rs.getString("nnn");


                return name;
            }

            rs.close();
            conn.close();

        } catch(ClassNotFoundException e) {
            System.out.println("Sorry,can`t find the Driver!");
            e.printStackTrace();
        } catch(SQLException e) {
            e.printStackTrace();
        } catch(Exception e) {
            e.printStackTrace();
        }


        return null;

    }

    public static List exec(String sql) {
        // 驱动程序名
        String driver = "com.mysql.jdbc.Driver";

        // URL指向要访问的数据库名scutcs
        String url = "jdbc:mysql://127.0.0.1:3306/tm_master";

        // MySQL配置时的用户名
        String user = "root";

        // MySQL配置时的密码
        String password = "082040";

        try {
            // 加载驱动程序
            Class.forName(driver);

            // 连续数据库
            Connection conn = DriverManager.getConnection(url, user, password);

            // statement用来执行SQL语句
            Statement statement = conn.createStatement();

            // 结果集
            ResultSet rs = statement.executeQuery(sql);

            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            String[] columnNames = new String[columnCount];
            for (int i = 0; i < columnCount; i++) {
                columnNames[i] = metaData.getColumnName(i + 1);
            }

            List list = new ArrayList();
            while(rs.next()) {
                Map map = new HashMap<>();
                for (String columnName : columnNames) {
                    map.put(columnName, rs.getObject(columnName));
                }

                list.add(map);
            }

            rs.close();
            conn.close();

            return list;
        } catch(ClassNotFoundException e) {
            System.out.println("Sorry,can`t find the Driver!");
            e.printStackTrace();
        } catch(SQLException e) {
            e.printStackTrace();
        } catch(Exception e) {
            e.printStackTrace();
        }


        return null;

    }


    public static List<CatData> getCidAndNameFromDB(int page) {
        // 驱动程序名
        String driver = "com.mysql.jdbc.Driver";

        // URL指向要访问的数据库名scutcs
        String url = "jdbc:mysql://127.0.0.1:3306/tm_master";

        // MySQL配置时的用户名
        String user = "root";

        // MySQL配置时的密码
        String password = "082040";

        try {
            // 加载驱动程序
            Class.forName(driver);

            // 连续数据库
            Connection conn = DriverManager.getConnection(url, user, password);

            // statement用来执行SQL语句
            Statement statement = conn.createStatement();

            // 要执行的SQL语句
            int offset = page * 100;
            String sql = "select cid,name from item_cat_copy limit "+offset+",100";

            // 结果集
            ResultSet rs = statement.executeQuery(sql);


            List<CatData> list = new ArrayList();
            while(rs.next()) {

                String cid = rs.getString("cid");
                String name = rs.getString("name");

                list.add(new CatData(cid, name));
            }

            rs.close();
            conn.close();

            return list;
        } catch(ClassNotFoundException e) {
            System.out.println("Sorry,can`t find the Driver!");
            e.printStackTrace();
        } catch(SQLException e) {
            e.printStackTrace();
        } catch(Exception e) {
            e.printStackTrace();
        }


        return null;

    }


    public static List<CatData> getChild(Long... cids) {
        // 驱动程序名
        String driver = "com.mysql.jdbc.Driver";

        // URL指向要访问的数据库名scutcs
        String url = "jdbc:mysql://127.0.0.1:3306/tm_master";

        // MySQL配置时的用户名
        String user = "root";

        // MySQL配置时的密码
        String password = "082040";

        try {
            // 加载驱动程序
            Class.forName(driver);

            // 连续数据库
            Connection conn = DriverManager.getConnection(url, user, password);

            if(!conn.isClosed())
                System.out.println("Succeeded connecting to the Database!");

            // statement用来执行SQL语句
            Statement statement = conn.createStatement();

            // 要执行的SQL语句
            StringBuilder sb = new StringBuilder();
            for (Long cid : cids) {
                sb.append(",").append(cid);
            }


            String sql = "select * from item_cat where parentCid in ("+sb.substring(1)+");";

            // 结果集
            ResultSet rs = statement.executeQuery(sql);


            List<CatData> list = new ArrayList();
            while(rs.next()) {

                String cid = rs.getString("cid");
                String name = rs.getString("name");
                Boolean isParent = rs.getBoolean("isParent");

                list.add(new CatData(cid, name, isParent));
            }

            rs.close();
            conn.close();

            return list;
        } catch(ClassNotFoundException e) {
            System.out.println("Sorry,can`t find the Driver!");
            e.printStackTrace();
        } catch(SQLException e) {
            e.printStackTrace();
        } catch(Exception e) {
            e.printStackTrace();
        }


        return null;

    }
}
